﻿/*----------------------------------------------------------------
    // Copyright (C) 2009 江苏华招网
    // 版权所有。 
    // 作者：庄金冬 
    //
    // 文件名：DAL_Area.cs
    // 文件功能描述：地区数据访问逻辑
    // 创建标识：庄金冬2009-03-06
    //
    // 修改标识：
    // 修改描述：
    //
    // 修改标识：
    // 修改描述：
    //
    // 审核人：
    // 审核时间：
    // 审核说明：
 //----------------------------------------------------------------*/
using System;
using System.Collections.Generic;

using System.Text;
using HZ.DrugTrade.Model;
using HZ.Common;
using System.Data;
using System.Data.SqlClient;
using HZ.Data;
namespace HZ.DrugTrade.DAL
{
    /// <summary>
    /// 地区数据访问逻辑
    /// </summary>
    public class DAL_Area
    {

        /// <summary>
        /// 条件地区查询
        /// </summary>
        /// <param name="where">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<Area> GetCompanyByWhere(HZ.DrugTrade.Model.Area query, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Area>(query,true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM TB_Area  WHERE 1=1 {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.Area>(dt);
        }
        /// <summary>
        /// 获取未选择的区域 by zdc
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static IList<Area> GetUnChoseArea(string where)
        {
            string sqlInfo = string.Format("SELECT * FROM TB_Area  WHERE 1=1  AND FatherAreaID = '150000' and AreaID not in (select AreaID from tb_PSSelectArea where companyPS_Id ='{0}') order by AreaId", where);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);
            return HZ.Data.DAL_Helper.CommonFillList<Area>(dt);
        }
        /// <summary>
        /// 中心端获取未选择和未提交的区域 by Meng 2013-07-04
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static IList<Area> GetUnChoseAreaByZBZX(string where)
        {
            string sqlInfo = string.Format("SELECT * FROM TB_Area  WHERE 1=1  AND FatherAreaID = '150000' and AreaID not in (select AreaID from tb_PSSelectArea where companyPS_Id ='{0}' and submitstatus <> '0') order by AreaId", where);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);
            return HZ.Data.DAL_Helper.CommonFillList<Area>(dt);
        }
        /// <summary>
        /// 增加配送企业选择的地区 by zdc
        /// </summary>
        /// <param name="areaIds">地区</param>
        /// <param name="userid">企业id</param>
        /// <returns>return true or flase</returns>
        public static bool AddChoseArea(List<string> areaIds,string userid)
        {
            bool flag = false;
            try
            {
                if (areaIds.Count > 0)
                {
                    for (int i = 0; i < areaIds.Count; i++)
                    {

                        string strsql = string.Format(@"select count(*) from TB_PSSelectArea where companyps_id='{0}' and areaid = '{1}'",userid,areaIds[i]);
                        if(Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(strsql)) > 0 )
                        {
                            return false;
                        }
                    }
                }
                string insertInfo = "insert into TB_PSSelectArea (companyPS_Id,areaId,checkStatus,submitStatus) ";

                if (areaIds.Count > 0)
                {
                    for (int i = 0; i < areaIds.Count; i++)
                    {
                        insertInfo += string.Format(" select '{0}','{1}',0,0 from dual union", userid, areaIds[i]);
                    }
                }
                int count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(insertInfo.Substring(0,insertInfo.Length-5));
                if (count>0)
                {
                    flag = true;
                }
                return flag;
            }
            catch (Exception)
            {
                return flag;
                throw;
            }
        }
        /// <summary>
        /// 根据配送企业id获取已选择的地区 by zdc
        /// </summary>
        /// <param name="userid">企业id</param>
        /// <returns>return datatable</returns>
        public static DataTable GetSelectArea(string userid,string projectid)
        {
            DataTable dt = new DataTable();
            try
            {
                string sqlInfo = string.Format("select * from View_SelectAreaManage where companyPs_Id='{0}' {1}", userid, projectid);
                dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);
                return dt;
            }
            catch (Exception)
            {
                return dt;
                throw;
            }
            
        }
        /// <summary>
        /// 处理已选择的区域 by zdc
        /// </summary>
        /// <param name="areaIds">地区id</param>
        /// <param name="type">类型：1提交 否则删除</param>
        /// <returns>return true or false</returns>
        public static bool DealSelectArea(string areaIds,int type,string userid)
        {
            bool flag = false;
            try
            {
                if (type == 1)//提交
                {
                    string sql = string.Format(@"select count(*) from TB_PSSelectArea where areaId in({0}) and submitStatus = 1 and companyps_id='{1}'", areaIds, userid);
                    if (Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql)) > 0)
                    {
                        return false;
                    }
                    else
                    {
                        string updateInfo = string.Format(@"BEGIN update TB_PSSelectArea set submitStatus=1,submitTime=sysdate where areaId in ({0}) and companyps_id='{1}';
                                                        insert into TB_AreaCompany_PS(companyid,Areaid,checkstatus) values('{1}',{0},0);end;", areaIds, userid);
                        int count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(updateInfo);
                        if (count > 0)
                        {
                            flag = true;
                        }
                    }
                }
                else//删除
                {
                    string deleteInfo = string.Format("delete TB_PSSelectArea where areaId in ({0}) and companyps_id='{1}'", areaIds,userid);
                    int count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(deleteInfo);
                    if (count > 0)
                    {
                        flag = true;
                    }
                }
                return flag;
            }
            catch (Exception)
            {
                return flag;
                throw;
            }
            
        }
        /// <summary>
        /// 获取要在本地区配送的企业 by zdc
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetComById(string where, int currentPage, int pageSize, out int totalRecord)
        {
            #region 改版前
//            string sqlInfo = string.Format(@" SELECT * FROM (
// SELECT *, ROW_NUMBER() OVER (ORDER BY  companyPS_Id ASC ) AS POS FROM (select * from View_SelectAreaManage where submitStatus=1 {0}) _Temp
// ) 
//_TB WHERE _TB.POS BETWEEN {1} AND {2}", where, (currentPage - 1) * pageSize + 1, currentPage * pageSize);
//            //HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("select * from View_SelectAreaManage where 1=1 {0} ", where), pageSize);
//            //pdes.CurrentPage = currentPage;
//            //totalRecord = pdes.TotalCount;

//            //DataTable dt = pdes.GetSpecailPage();
//            totalRecord = int.Parse(HZ.Data.DbHelperFactory.Create().ExecuteScalar(string.Format("select count(*) from View_SelectAreaManage where 1=1 {0}", where)).ToString());
//            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);
//            return dt; 
            #endregion

            #region 改版后 by sinodier 2011年1月10日12:56:51
//            string sqlInfo = string.Format(@" SELECT * FROM (
// SELECT *, ROW_NUMBER() OVER (ORDER BY  companyPS_Id ASC ) AS POS FROM (select * from View_SelectAreaManage where submitStatus=1 {0}) _Temp
// ) 
//_TB WHERE _TB.POS BETWEEN {1} AND {2}", where, (currentPage - 1) * pageSize + 1, currentPage * pageSize);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("select distinct companyps_id,companyName from View_SelectAreaManage where 1=1 {0} and companyps_id in (select companyid from tb_companyinfo where checkstatus=1)", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            //totalRecord = int.Parse(HZ.Data.DbHelperFactory.Create().ExecuteScalar(string.Format("select count(*) from View_SelectAreaManage where 1=1 {0}", where)).ToString());
            //DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);
            return dt;
            #endregion
        }
        /// <summary>
        /// 审核在本地区备案的企业 By zdc
        /// </summary>
        /// <param name="companyid"></param>
        /// <param name="checkType"></param>
        /// <returns></returns>
        public static bool CheckEnterPrise(string companyid,int checkType,string userId)
        {
            bool flag = false;
            try
            {
                string updateInfo = "";
                if (checkType==1)//审核通过
                {
                    updateInfo = string.Format("update TB_PSSelectArea set checkStatus=1,checkTime=sysdate where companyPS_Id in ({0}) and areaId in (select areaId from TB_ManageOrg where ManageOrgID='{1}')", companyid, userId);
                    
                }
                else//审核不通过
                {
                    updateInfo = string.Format("update TB_PSSelectArea set checkStatus=2,checkTime=sysdate where companyPS_Id in ({0}) and areaId in (select areaId from TB_ManageOrg where ManageOrgID='{1}')", companyid, userId);
                    
                }
                int count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(updateInfo);
                if (count > 0)
                {
                    flag = true;
                }
                return flag;
            }
            catch (Exception)
            {
                return flag;
                throw;
            }
            
        }
        /// <summary>
        /// 内蒙招标中心（zbzx）审核备案的企业 by zdc
        /// </summary>
        /// <param name="companyIds"></param>
        /// <param name="areaIds"></param>
        /// <param name="checkType"></param>
        /// <returns></returns>
        public static bool CheckAllEnterPrise(string companyIds, string areaIds,int checkType)
        {
            bool flag = false;
            try
            {
                string updateInfo = "";
                if (checkType == 1)//审核通过
                {
                    updateInfo = string.Format("update TB_PSSelectArea set checkStatus=1,checkTime=sysdate where companyPS_Id in ({0}) and  areaId in ({1})", companyIds, areaIds);
                }
                else//审核不通过
                {
                    updateInfo = string.Format("update TB_PSSelectArea set checkStatus=2,checkTime=sysdate where companyPS_Id in ({0}) and  areaId in ({1})", companyIds, areaIds);
                }
                int count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(updateInfo);
                if (count > 0)
                {
                    flag = true;
                }
                return flag;
            }
            catch (Exception)
            {
                return flag;
                throw;
            }
            
        }
        /// <summary>
        /// 根据企业的id获取所有中心和盟市备案审核通过的地区 by zdc
        /// </summary>
        /// <param name="CompanyPS_Id"></param>
        /// <returns></returns>
        public static string GetNeedCity(string CompanyPS_Id,string projectid)
        {
            string allAreaIds = "";
            try
            {
                string sqlInfo = "";
                if (projectid == "0001")
                {
                    sqlInfo = string.Format("select * from TB_PSSelectArea where companyPS_Id ='{0}' and checkByZbzx=1 and ishcygcg is null", CompanyPS_Id);
                }
                else
                {
                    sqlInfo = string.Format("select * from TB_PSSelectArea where companyPS_Id ='{0}' and checkByZbzx=1 and ishcygcg = 1", CompanyPS_Id);
                }
                DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        allAreaIds += dt.Rows[i]["areaId"].ToString() + ",";
                    }
                }
                return allAreaIds.Substring(0, allAreaIds.Length - 1);
            }
            catch (Exception)
            {
                return "";
                throw;
            }
            

            
        }


        /// 获取要在本地区配送的企业 by zdc
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetComByIdMS(string where, int currentPage, int pageSize, out int totalRecord)
        {
            
            // string sqlInfo = string.Format(@" SELECT * FROM (
            //       SELECT *, ROW_NUMBER() OVER (ORDER BY  companyPS_Id ASC ) POS FROM (select * from View_SelectAreaManage where submitStatus=1 {0}) _Temp
            //              ) _TB WHERE _TB.POS BETWEEN {1} AND {2}", where, (currentPage - 1) * pageSize + 1, currentPage * pageSize);

            //totalRecord = int.Parse(HZ.Data.DbHelperFactory.Create().ExecuteScalar(string.Format("select count(*) from (select * from View_SelectAreaManage where 1=1 {0}) a", where)).ToString());
            //DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);


            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("select * from View_SelectAreaManage where submitStatus=1 {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();          

            return dt;
        }
        /// <summary>
        /// 根据条件获取区域 by zdc
        /// </summary>
        /// <param name="companyids"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string GetAreasByWhere(string companyids, int type)
        {
            string areas = "";
            string sql = string.Format("select areaid,areaname,checkbyzbzx from View_SelectAreaManage where companyps_id='{0}' and checkbyzbzx={1} and submitstatus=1 ",companyids,type);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            if (dt.Rows.Count>0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    areas += dt.Rows[i]["areaname"].ToString() + ",";
                }
            }
            if (areas!="")
            {
                return areas.Substring(0, areas.Length - 1);
            }
            else
            {
                return areas;
            }
        }
        /// <summary>
        /// 中心端查询企业配送区域 add by Meng 2013-07-01
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetCompanyAreaidByCompanyid(string where, int currentPage, int pageSize, out int totalRecord)
        {
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"select companyid,companyname,max(case areaid when '150100' then '呼和浩特市'end)||max(case areaid when '150100' then ',' end)||max(case areaid when '150200' then '包头市'end)||max(case areaid when '150200' then ',' end)||max(case areaid when '150300' then '乌海市'end)||max(case areaid when '150300' then ',' end)||max(case areaid when '150400' then '赤峰市'end)||max(case areaid when '150400' then ',' end)||max(case areaid when '150500' then '通辽市'end)||max(case areaid when '150500' then ',' end)||max(case areaid when '150600' then '鄂尔多斯市'end)
||max(case areaid when '150600' then ',' end)||max(case areaid when '150700' then '呼伦贝尔市'end)||max(case areaid when '150700' then ',' end)||max(case areaid when '150800' then '巴彦淖尔市'end)||max(case areaid when '150800' then ',' end)||max(case areaid when '150900' then '乌兰察布市'end)
||max(case areaid when '150900' then ',' end)||max(case areaid when '152200' then '兴安盟'end) ||max(case areaid when '152200' then ',' end)||max(case areaid when '152500' then '锡林郭勒盟'end) ||max(case areaid when '152500' then ',' end)||max(case areaid when '152900' then '阿拉善盟'end)  as 配送区域 
from TB_PSSelectArea a right join (select companyid,companyname from tb_company where companyid like 'QP%') b on a.companyps_id = b.companyid where 1=1 {0}
group by companyid,companyname ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();

            return dt;
        }
        /// <summary>
        /// 中心端编辑配送区域 add by Meng 2013-07-01
        /// </summary>
        /// <param name="areaIds">地区</param>
        /// <param name="userid">企业id</param>
        /// <returns>return true or flase</returns>
        public static bool UpdateComapnyAreaByCompanyid(List<string> areaIds, string userid)
        {
            bool flag = false;
            try
            {
                //if (areaIds.Count > 0)
                //{
                //    for (int i = 0; i < areaIds.Count; i++)
                //    {

                //        string strsql = string.Format(@"select count(*) from TB_PSSelectArea where companyps_id='{0}' and areaid = '{1}'", userid, areaIds[i]);
                //        if (Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(strsql)) > 0)
                //        {
                //            return false;
                //        }
                //    }
                //}
                string sql = string.Format(@"delete from TB_PSSelectArea where companyPS_Id ='{0}' and submitstatus ='0'", userid);
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
                string insertInfo = "begin";
                    if (areaIds.Count > 0)
                    {
                        for (int i = 0; i < areaIds.Count; i++)
                        {
                            insertInfo += string.Format(" insert into TB_PSSelectArea (companyPS_Id,areaId,checkStatus,submitStatus) select '{0}','{1}',0,1 from dual; insert into TB_AreaCompany_PS(companyid,Areaid,checkstatus) values('{0}',{1},0); ", userid, areaIds[i]);
                        }
                    }
                    insertInfo += " end;";
                    int count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(insertInfo);
                    if (count > 0)
                    {
                        flag = true;
                    }
                return flag;
            }
            catch (Exception)
            {
                return flag;
                throw;
            }
        }
    }
}
